package fr.esisar.picaso.requetes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import fr.esisar.picaso.entites.Adresse;
import fr.esisar.picaso.entites.ClientAuthentifie;
import fr.esisar.picaso.moteur.Connexion;
import fr.esisar.picaso.moteur.Crypteur;


public class RequetesClientAuthentifie {

	public RequetesClientAuthentifie() {
		
	}
	
	public ClientAuthentifie authentifierClient(String email, String password) throws SQLException {
		Connection connexion = Connexion.getAdminConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT * FROM U0.clientAuthentifie WHERE email LIKE '" + email + "' AND password LIKE '" + password + "'";
		ResultSet rs = st.executeQuery(requete);
		
		ClientAuthentifie client = null;
		while(rs.next()) {
			client = new ClientAuthentifie(rs.getInt("idClient"), rs.getString("email"), rs.getString("nom"), rs.getString("prenom"), rs.getString("password"), (new RequetesAdresse()).getAdresse(rs.getInt("idAdresse")));
		}
		
		Connexion.terminerConnexion();
		return client;
	}
	
	public ClientAuthentifie inscrireClient(ClientAuthentifie client) throws SQLException {
		Adresse adresse = client.getAdresse();

		Connection connexion = Connexion.getAdminConnection();
		Statement st = connexion.createStatement();
				
		Crypteur crypteur = new Crypteur();
		
		
		String requete = "SELECT MAX(idAdresse) FROM adresse";
		ResultSet rs = st.executeQuery(requete);
		
		rs.next();
		int idAdresse = rs.getInt("MAX(idAdresse)")+1;
		
		requete = "INSERT INTO adresse(idAdresse , numero, rue, CP, ville, pays) VALUES ("+ idAdresse +", '"+ adresse.getNumero() +"', '"+ adresse.getRue() +"', "+ adresse.getCode_postal() +", '"+ adresse.getVille() +"', '"+ adresse.getPays() +"') ";
		rs = st.executeQuery(requete);
		
		requete = "SELECT MAX(idClient) FROM clientAuthentifie";
		rs = st.executeQuery(requete);
		
		rs.next();
		int idClient = (rs.getInt("MAX(idClient)")+1);
		requete = "INSERT INTO ClientAuthentifie(idClient , email, nom, prenom, password, idAdresse) VALUES ("+ idClient +", '" + client.getEmail() + "', '" + client.getNom() + "', '" + client.getPrenom() + "', '" + client.getPassword() + "', " + idAdresse + ")";
		rs = st.executeQuery(requete);
	
		String emailCrypte =  crypteur.crypter(idClient);
		
		requete = "CREATE USER " + emailCrypte + " IDENTIFIED BY " + client.getPassword();
		rs = st.executeQuery(requete);
		
		requete = "GRANT CREATE SESSION TO " + emailCrypte;
		rs = st.executeQuery(requete);
		
		requete = "GRANT all ON Adresse TO " + emailCrypte;
		rs = st.executeQuery(requete);
		
		requete = "GRANT all ON ClientAuthentifie TO " + emailCrypte;
		rs = st.executeQuery(requete);
			
		requete = "GRANT all ON Photo TO " + emailCrypte;
		rs = st.executeQuery(requete);
		
		requete = "GRANT all ON Album TO " + emailCrypte;
		rs = st.executeQuery(requete);
		requete = "GRANT all ON Commande TO " + emailCrypte;
		rs = st.executeQuery(requete);
		requete = "GRANT all ON Reference TO " + emailCrypte;
		rs = st.executeQuery(requete);
		requete = "GRANT all ON Format TO " + emailCrypte;
		rs = st.executeQuery(requete);
		requete = "GRANT all ON Compose TO " + emailCrypte;
		rs = st.executeQuery(requete);

		connexion.commit();
		Connexion.terminerConnexion();

		
		return client;
	}
	
	public ClientAuthentifie getClient(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT * FROM U0.clientAuthentifie WHERE idClient="+id;
		ResultSet rs = st.executeQuery(requete);
		
		ClientAuthentifie client = null;
		while(rs.next()) {
			client = new ClientAuthentifie(id, rs.getString("email"), rs.getString("nom"), rs.getString("prenom"), rs.getString("password"), (new RequetesAdresse()).getAdresse(rs.getInt("idAdresse")));
		}
		
		return client;
	}
	
	public void setClient(ClientAuthentifie client) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.clientAuthentifie SET email='"+client.getEmail()+"', nom='"+client.getNom()+"', prenom='"+client.getPrenom()+"', password='"+client.getPassword()+", idAdresse='"+client.getAdresse().getId()+"' WHERE idClient="+client.getId();
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getEmail(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT email FROM U0.clientAuthentifie WHERE idClient="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String email = null;
		while(rs.next()) {
			email = rs.getString("email");
		}
		
		return email;
	}
	
	public void setEmail(int id, String email) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.ClientAuthentifie SET email='"+email+"' WHERE idClient="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getNom(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT nom FROM U0.clientAuthentifie WHERE idClient="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String nom = null;
		while(rs.next()) {
			nom = rs.getString("nom");
		}
		
		return nom;
	}
	
	public void setNom(int id, String nom) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.ClientAuthentifie SET nom='"+nom+"' WHERE idClient="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getPrenom(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT prenom FROM U0.clientAuthentifie WHERE idClient="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String prenom = null;
		while(rs.next()) {
			prenom = rs.getString("prenom");
		}
		
		return prenom;
	}
	
	public void setPrenom(int id, String prenom) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.ClientAuthentifie SET prenom='"+prenom+"' WHERE idClient="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getPassword(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT password FROM U0.clientAuthentifie WHERE idClient="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String password = null;
		while(rs.next()) {
			password = rs.getString("password");
		}
		
		return password;
	}
	
	public void setPassword(int id, String password) throws SQLException{
		Connection connexion = Connexion.getAdminConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.ClientAuthentifie SET password='"+password+"' WHERE idClient="+id;
		st.executeQuery(requete);
		
		Crypteur crypteur = new Crypteur();
		
		requete = "ALTER USER "+ crypteur.crypter(id) +" IDENTIFIED BY "+ password;
		st.executeQuery(requete);
		
		connexion.commit();
		Connexion.terminerConnexion();
	}
	
}
